
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Gruppe 4
 * @version 20-12-2011
 */

public class DBOrder implements IFDBOrder 
{
    private Connection con;
    PreparedStatement pStmtSelect;
    
    /** 
     * Creates a new instance of DBOrder 
     */
    public DBOrder() 
    {
      con = DBConnection.getInstance().getDBcon();
    }
    
    //Implements the methods from the interface
    
    @Override
    public ArrayList<Order> findOrders(int personId, boolean retrieveAssociation)
    {
        String wClause = "  where customerId = ";
        return findOrdersWithPreQuery(personId, wClause, retrieveAssociation);
    }
    
    /**
     * The method
     * @param orderId
     * @param retrieveAssociation
     * @return 
     */
    @Override
    public ArrayList<Order> findOrdersByOrderId(int orderId, boolean retrieveAssociation)
    {
        String wClause = "  where orderId = ";
        return findOrdersWithPreQuery(orderId, wClause, retrieveAssociation);
    }
    
    /**
     * Method that finds an order from the database with a specific orderId
     * @param orderId
     * @return returns an Order object
     */
    @Override
    public Order searchOrderId(int orderId, boolean retrieveAssociation)
    {
        String wClause = "  where o.orderId = ";
        Order orderObj = new Order();
        return findWithPreQuery(orderId, wClause, retrieveAssociation);
        
       
    }
    /**
     * Method that finds an order from the database with a specific orderDate
     * @param orderDate
     * @return returns an ArrayList of Order objects
     */
    @Override
    public ArrayList<Order> searchOrderDate(String orderDate, boolean retrieveAssociation)
    {
        String wClause = "  where o.orderDate = ";
        ArrayList<Order> orderObjs = new ArrayList<Order>();
        orderObjs = findOrdersWithPreQuery(orderDate, wClause, retrieveAssociation);
        return orderObjs;
    }
       
    /**
     * Method that inserts an order into the database
     * @param orderObj
     * @return 
     */
    @Override
    public int insertOrder(Order orderObj) throws Exception
    {
        int rc = -1;
        
        Customer customer = null;
        Employee employee = null;
        
        //call to get the next order id
        int nextOrderId = getMaxId();
        nextOrderId = nextOrderId + 1;
        System.out.println("next order id = " +  nextOrderId);
        
        PreparedStatement pstmt = null;
        
        String insert = "insert into orders(orderId, tenderDate, tenderStatus, totalPrice, customerId, employeeId)"+
                     "values (?,?,?,?,?,?)";
        System.out.println(insert);
        
        
            pstmt = con.prepareStatement(insert);
            
            pstmt.setInt(1,nextOrderId);
            pstmt.setString(2,orderObj.getTenderDate());
            pstmt.setInt(3, orderObj.getTenderStatus());
            pstmt.setDouble(4, orderObj.getTotalPrice());
            
            System.out.println("test");
            
            if(orderObj.getCustomer() != null && orderObj.getEmployee() != null)
            {
                customer = (Customer) orderObj.getCustomer();
                employee = (Employee) orderObj.getEmployee();
            }
            
            pstmt.setInt(5, customer.getPersonId());
            pstmt.setInt(6, employee.getPersonId());
            
            rc = pstmt.executeUpdate();
               
        return rc;
    }
    
    /**
     * Method that updates information about an order
     * Not yet implemented as prepared statement
     * @param order
     * @return 
     */
    @Override
    public int updateOrder(Order order)
    {
        Order orderObj  = order;
        int rc=-1;
        
        String query="UPDATE order SET "+
                "orderDate ='"+ orderObj.getOrderDate()+"', "+
                "tenderDate ='"+ orderObj.getTenderDate() + "', "+
                "tenderStatus ='"+ orderObj.getTenderStatus() + "', "+
                "totalPrice ='"+ orderObj.getTotalPrice() + "', "+
               // "personId ='"+ orderObj.getCustomer().getCustomerId() + "', "+
               
                "WHERE orderId = '"+ orderObj.getOrderId() + "'";
        
        System.out.println("Update query:" + query);
        rc= updateQuery(rc, query);
        return(rc);	
    }
    
    /**
     * Method that deletes an order from the database
     * @param orderId
     * @return 
     */
    @Override
    public int deleteOrder(int orderId)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "delete from order where orderId = ?";
        System.out.println(delete);
        try
        {
            pstmt = con.prepareStatement(delete);
            pstmt.setInt(1,orderId);
            rc = pstmt.executeUpdate(); 
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return rc;
    }
            
    /**
     * 
     * @param rc
     * @param query
     * @return 
     */
    private int updateQuery(int rc, String query)
    {
        try
        {
            //insert new order
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }//end try
        catch(Exception ex)
        {
            System.out.println("Insert exception in Order database: "+ex);
        }//end catch
        
        return rc;  
    }
    
    /**
     * 
     * @param results
     * @param retrieveAssociation
     * @return 
     */
    private Order buildOrder(ResultSet results, boolean retrieveAssociation)
    {
        Order orderObj = new Order();
        try
        {
            orderObj.setOrderId(results.getInt("orderId"));
            orderObj.setOrderDate(results.getString("orderDate"));
            orderObj.setTenderDate(results.getString("TenderDate"));
            orderObj.setTenderStatus(results.getInt("TenderStatus"));
            orderObj.setTotalPrice(results.getDouble("totalPrice"));
            //orderObj.setInvoice(new Invoice(results.getInt("invoiceNo")));
            orderObj.setCustomer(new Customer(results.getInt("customerId")));
            orderObj.setEmployee(new Employee(results.getInt("employeeId")));
        }
        catch(Exception e)
        {
            System.out.println("error in building the supplier object");
            System.out.println(e.getMessage());
        }
        return orderObj;
    }

    /**
     * Method that creates a prepared statement to find an order with a given orderId
     * @return returns the prepared statement
     */
    private PreparedStatement getPreparedStmt(String wClause)
    {
        try
        {
            String query = "SELECT * FROM Orders as o " +
                    wClause + " ?";
            
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
        }//end try
        catch(Exception e)
        {
            e.getMessage();
        }
        return pStmtSelect;
    }
    
    /**
     * 
     * @param value
     * @param wClause
     * @param retrieveAssociation
     * @return 
     */
    public Order findWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
    {
        System.out.println("Find with preCompiled");
        int orderId = 0;
        String orderDate = "";
            
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);          
        }
        Order ordObj = null;
        ResultSet results;
        try
        {
            if(value instanceof Integer)
            {
                orderId = (Integer) value;
                pStmtSelect.setInt(1,orderId);
            }
            if(value instanceof String)
            {
                orderDate = (String) value;
                pStmtSelect.setString(1,orderDate);
            }
           
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                ordObj = buildOrder(results, retrieveAssociation);
            }
            if(retrieveAssociation)
            {
                    IFDBPerson dbPersonObj = new DBPerson();
                    Customer cus = (Customer) ordObj.getCustomer();
                    Customer cusObj = (Customer) dbPersonObj.findPerson(cus.getPersonId(), false, false);
                    System.out.println("Customer is seleceted ");
                    ordObj.setCustomer(cusObj);
                    
                    Employee emp = (Employee) ordObj.getEmployee();
                    Employee empObj = (Employee) dbPersonObj.findPerson(emp.getPersonId(), false, true);
                    System.out.println("Employee is seleceted ");
                    ordObj.setCustomer(empObj);
                        
                    IFDBTask dbTaskObj = new DBTask();
                    ArrayList<Task> taskObj = dbTaskObj.findTasksByOrderId(ordObj.getOrderId(), true);
                    System.out.println("Tasks is selected - test");
                    ordObj.setArrayTasks(taskObj);
            }
        } 
        catch(Exception e)
        {
            e.getMessage();
        }
        
        return ordObj;
    }
    
    /**
     * 
     * @param value
     * @param wClause
     * @param retrieveAssociation
     * @return 
     */
    public ArrayList<Order> findOrdersWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
    {
        System.out.println("Find with preCompiled");
        int orderId = 0;
        String orderDate = "";
            
        if(pStmtSelect == null)
        {
            pStmtSelect =  getPreparedStmt(wClause);          
        }
        ArrayList<Order> list = new ArrayList<Order>();
        ResultSet results;
        try
        {
            if(value instanceof Integer)
            {
                orderId = (Integer) value;
                pStmtSelect.setInt(1,orderId);
            }
            if(value instanceof String)
            {
                orderDate = (String) value;
                pStmtSelect.setString(1,orderDate);
            }
           
            results = pStmtSelect.executeQuery();
            while( results.next() )
            {
                Order orderObj = new Order();
		orderObj = buildOrder(results, false);	
                list.add(orderObj);
            }//end while
            if(retrieveAssociation)
            {   
                for(Order ordObj : list)
                { 
                    System.out.println("Test");
                    IFDBPerson dbPersonObj = new DBPerson();
                    Customer cus = (Customer) ordObj.getCustomer();
                    Customer cusObj = (Customer) dbPersonObj.findPerson(cus.getPersonId(), false, false);
                    System.out.println("Customer is seleceted ");
                    ordObj.setCustomer(cusObj);
                    
                    Employee emp = (Employee) ordObj.getEmployee();
                    Employee empObj = (Employee) dbPersonObj.findPerson(emp.getPersonId(), false, true);
                    System.out.println("Employee is seleceted ");
                    ordObj.setCustomer(empObj);
                        
                    IFDBTask dbTaskObj = new DBTask();
                    ArrayList<Task> taskObj = dbTaskObj.findTasksByOrderId(ordObj.getOrderId(), true);
                    System.out.println("Tasks is selected - test");
                    ordObj.setArrayTasks(taskObj);  
                    
                    //tilføj også invoice
                 }
            }//end if  
            
        } 
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return list;
    }
       
    /**
     * Method to get the highest orderId that excist in the database
     * @return returns the highest orderId
     */
    @Override
    public int getMaxId()
    {
        int maxOrderId = GetMax.getMaxId("Select max(orderId) from orders");
        return maxOrderId;
    }
   
}